package jdbc.datasource;

import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.ArrayList;

/**
 * @author DCSGO
 * @version 1.0
 * JDBCUtilsByDruid 的使用
 */
public class JDBCUtilsByDruid_USE {
    @Test
    public void testJDBCUtilsByDruid() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            //delete
            preparedStatement = connection.prepareStatement("delete from news");
            int rows = preparedStatement.executeUpdate();
            System.out.println(rows > 0 ? "delete 成功!" : "delete 失败!");
            JDBCUtilsByDruid.close(null, preparedStatement, null);

            //insert
            preparedStatement = connection.prepareStatement("insert into news values (null,?)");
            for (int i = 0; i < 10; i++) {
                preparedStatement.setString(1, "新闻_" + i);
                preparedStatement.addBatch();
            }
            int[] ints = preparedStatement.executeBatch();
            boolean isChange = true;
            for (int i : ints) {
                if (i == 0) {
                    isChange = false;
                    break;
                }
            }
            System.out.println(isChange ? "insert 成功!" : "insert 失败!");
            preparedStatement.clearBatch();
            JDBCUtilsByDruid.close(null, preparedStatement, null);

            //update
            preparedStatement = connection.prepareStatement("update news set content = '北京新闻' where content = '新闻_5'");
            rows = preparedStatement.executeUpdate();
            System.out.println(rows > 0 ? "update 成功!" : "update 失败!");
            JDBCUtilsByDruid.close(null, preparedStatement, null);

            //select
            preparedStatement = connection.prepareStatement("select * from news");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String content = resultSet.getString("content");
                System.out.println(id + "\t" + content);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        } finally {
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        }
        /*
         * 十月 03, 2023 9:35:25 上午 com.alibaba.druid.pool.DruidDataSource info
         * 信息: {dataSource-1} inited
         * delete 成功!
         * insert 成功!
         * update 成功!
         * 46	新闻_0
         * 47	新闻_1
         * 48	新闻_2
         * 49	新闻_3
         * 50	新闻_4
         * 51	北京新闻
         * 52	新闻_6
         * 53	新闻_7
         * 54	新闻_8
         * 55	新闻_9
         *
         * 进程已结束，退出代码为 0
         */
    }

    /*
    返回 resultSet 问题
    1.结果集合connection是关联的，即如果关闭连接,就不能使用结果集
    2.结果集不利于数据管理[只能用一次]
    3.使用返回信息也不方便
     */

    //使用土办法将 resultSet 中的数据保存到一个集合中
    //1.创建一个与performer表关联的类
    public ArrayList<Performer> getPerformers() throws SQLException {
        //2.获取包含表中所有数据的 resultSet
        Connection connection = JDBCUtilsByDruid.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select * from performer");
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<Performer> list = new ArrayList<>();

        //3.遍历 resultSet 并将数据保存到 list 中
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String sex = resultSet.getString("sex");
            Date birthday = resultSet.getDate("birthday");
            String phone_number = resultSet.getString("phone_number");
            list.add(new Performer(id, name, sex, birthday, phone_number));
        }
        //4.中转完毕，现在就可以关闭 resultSet 了
        JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        return list;
    }

    @Test
    public void testSaveResultSet() {
        try {
            ArrayList<Performer> performers = getPerformers();
            for (Performer p : performers) {
                System.out.println(p.getId() + "\t" +
                        p.getName() + "\t" +
                        p.getSex() + "\t" +
                        p.getBirthday() + "\t" +
                        p.getPhone_number());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
